package com.pai4j.user.repository.dao;
import com.pai4j.user.repository.entity.AccountRelationEntity;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface IAccountRelationDAO extends
        JpaSpecificationExecutor<AccountRelationEntity>,
        JpaRepository<AccountRelationEntity, Long> {


    AccountRelationEntity findAllBySourceAccountAndTargetAccount(String sourceAccount, String targetAccount);


    @Query(nativeQuery = true,
           value = "select * from " +
                   "pai4j_account_relation " +
                   "where " +
                   "type = :type " +
                   "and status = :status " +
                   "and (source_account = :account or target_account = :account)")
    List<AccountRelationEntity> getAccountRelations(String account, Integer type, Integer status);

    /**
     * 统计用户的粉丝数量（作为target_account的关系数量）
     *
     * @param targetAccount 目标账号
     * @param type 关系类型
     * @param status 关系状态
     * @return 粉丝数量
     */
    @Query(nativeQuery = true,
           value = "select count(*) from " +
                   "pai4j_account_relation " +
                   "where " +
                   "target_account = :targetAccount " +
                   "and type = :type " +
                   "and status = :status")
    Long countFollowers(String targetAccount, Integer type, Integer status);

    /**
     * 统计用户的关注数量（作为source_account的关系数量）
     *
     * @param sourceAccount 源账号
     * @param type 关系类型
     * @param status 关系状态
     * @return 关注数量
     */
    @Query(nativeQuery = true,
           value = "select count(*) from " +
                   "pai4j_account_relation " +
                   "where " +
                   "source_account = :sourceAccount " +
                   "and type = :type " +
                   "and status = :status")
    Long countFollowing(String sourceAccount, Integer type, Integer status);

    /**
     * 查询用户的未读待审核好友申请列表
     * 按创建时间倒序排列
     *
     * @param targetAccount 目标账号（待审核接收人）
     * @param type 关系类型
     * @param status 关系状态（WAIT_CONFIRMED）
     * @return 未读的待审核申请列表
     */
    @Query(nativeQuery = true,
           value = "select * from " +
                   "pai4j_account_relation " +
                   "where " +
                   "target_account = :targetAccount " +
                   "and type = :type " +
                   "and status = :status " +
                   "and is_read = false " +
                   "order by create_date desc")
    List<AccountRelationEntity> findUnreadPendingRelations(String targetAccount, Integer type, Integer status);

    /**
     * 查询用户的所有待审核好友申请列表（包括已读和未读）
     * 按创建时间倒序排列，用于从数据库恢复缓存
     *
     * @param targetAccount 目标账号（待审核接收人）
     * @param type 关系类型
     * @param status 关系状态（WAIT_CONFIRMED）
     * @return 所有待审核申请列表
     */
    @Query(nativeQuery = true,
           value = "select * from " +
                   "pai4j_account_relation " +
                   "where " +
                   "target_account = :targetAccount " +
                   "and type = :type " +
                   "and status = :status " +
                   "order by create_date desc")
    List<AccountRelationEntity> findAllPendingRelations(String targetAccount, Integer type, Integer status);

    /**
     * 批量标记待审核申请为已读
     *
     * @param targetAccount 目标账号（待审核接收人）
     * @param type 关系类型
     * @param status 关系状态（WAIT_CONFIRMED）
     * @return 更新的记录数
     */
    @Query("UPDATE AccountRelationEntity a SET a.isRead = true " +
           "WHERE a.targetAccount = :targetAccount " +
           "AND a.type = :type " +
           "AND a.status = :status " +
           "AND a.isRead = false")
    @org.springframework.data.jpa.repository.Modifying
    @org.springframework.transaction.annotation.Transactional
    int markPendingRelationsAsRead(String targetAccount, Integer type, Integer status);

    /**
     * 统计用户未读的待审核好友申请数量
     *
     * @param targetAccount 目标账号（待审核接收人）
     * @param type 关系类型
     * @param status 关系状态（WAIT_CONFIRMED）
     * @return 未读待审核数量
     */
    @Query(nativeQuery = true,
           value = "select count(*) from " +
                   "pai4j_account_relation " +
                   "where " +
                   "target_account = :targetAccount " +
                   "and type = :type " +
                   "and status = :status " +
                   "and is_read = false")
    Long countUnreadPendingRelations(String targetAccount, Integer type, Integer status);

}
